package com.web.test.dao;

import com.web.test.bean.Message;
import com.web.test.bean.Student;
import com.web.test.utils.JDBCUtil;
import com.web.test.utils.SQLUtils;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class StudentDao {



    public List<Student> find(String sql)
            throws ClassNotFoundException, SQLException, IOException {

        Connection connection = JDBCUtil.getConnection();
        Statement statement = connection.createStatement();
        ResultSet rSet = statement.executeQuery(sql);

        List<Student> msList = getStudentsByRS(rSet);

        System.out.println(sql);

        JDBCUtil.close(connection, statement, rSet);

        return msList;

    }


    public List<Student> page(int countPage, int whichPage)
            throws ClassNotFoundException, SQLException, IOException {

        Connection connection = JDBCUtil.getConnection();
        String sql = SQLUtils.stuAll;
        Statement statement = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = statement.executeQuery(sql);


        rs.last();
        int totalCount = rs.getRow();

        int start = (whichPage - 1) * countPage + 1;
        int end = (totalCount <= start + countPage ? totalCount : start
                + countPage);

        rs.absolute(start);// 将游标移动到当前页起始位置
        statement.setMaxRows(end);// 设置最大记录数为为当前页的下标
        List<Student> msList = new ArrayList<Student>();

        for (int i = 0; i < countPage; i++) {

            if (rs.isAfterLast()){

                break;

            }else {

                Student student = new Student(rs.getString("stu_no"), rs.getString("STU_NAME"),
                        rs.getInt("age"), rs.getString("password"), rs.getString("major"), rs.getString("college"));
                msList.add(student);
                rs.next();


            }
        }

        JDBCUtil.close(connection, statement, rs);


        return msList;
    }


    private List<Student> getStudentsByRS(ResultSet rSet) throws SQLException {

        List<Student> msList = new ArrayList<Student>();

         while (rSet.next()){

            int age = rSet.getInt("age");
            String nameM = rSet.getString("stu_name");
            String no = rSet.getString("stu_no");
            String password = rSet.getString("password");
            String major = rSet.getString("major");
            String college = rSet.getString("college");

             Student student = new Student(no, nameM, age, password, major, college);
            msList.add(student);
        }
        return msList;
    }

    public boolean updateAndDelete(String sql)
            throws ClassNotFoundException, SQLException, IOException {

        boolean flag = false;

        Connection connection = JDBCUtil.getConnection();

        Statement statement = connection.createStatement();

        if (statement.executeUpdate(sql) > 0) {
            JDBCUtil.close(connection, statement, null);
            flag = true;
        }
        return flag;

    }

}
